package sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import core.Aluno;
import core.Curso;
import core.Funcionario;
import core.Pessoa;
import core.Professor;
import core.Turma;

public class BancoDados {
	
	private String driverJDBC = "com.mysql.jdbc.Driver";
	private String dbMySQL = "jdbc:mysql://localhost/sawDB";
	private Connection con;
	
	public BancoDados( String usuario, String senha ) {
		try {
			Class.forName(this.driverJDBC);
			con = DriverManager.getConnection(dbMySQL, usuario, senha);
			con.setAutoCommit( false );
		} catch ( SQLException e1 ) {
			e1.printStackTrace();
		} catch ( ClassNotFoundException e2 ) {
			e2.printStackTrace();
		}
		criaTabelas();
	}
	
	public void close() {
		try {
			Class.forName(driverJDBC);
			con.commit();
			con.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public boolean addAluno( Aluno novoAluno ) {
		String comando = "INSERT INTO aluno VALUES (" +
			"NULL," +
			"'" + novoAluno.getNome() + "'," +
			"'" + novoAluno.getMatricula() + "'," +
			"NULL," +
			"NULL," +
			"NULL" +
			");";
		if( getAlunoID( novoAluno ) >= 0 ) {
			return false;
		}
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	
	public boolean addProfessor( Professor novoProfessor ) {
		String comando = "INSERT INTO professor VALUES ( NULL,'" + novoProfessor.getNome() + "','" + novoProfessor.getMatricula() + "');";
		if( getProfessorID( novoProfessor ) >= 0 ) {
			return false;
		}
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	
	public boolean addTurma( Turma novaTurma ) {
		System.out.println("antes");
		String comando = "INSERT INTO turma (nome,id_curso) VALUES ( '" + novaTurma.getNome() + "', " + getCursoID( novaTurma.getCurso() ) + " ) ;";
		System.out.println("depois \n" + comando);
		Statement st;
		if( getTurmaID( novaTurma ) < 0 ) {
			try {
				st = con.createStatement();
				st.execute( comando );
				con.commit();
			} catch( SQLException e ) {
				e.printStackTrace();
				return false;
			}
		} else {
			return false;
		}
		return true;
	}
	
	public ArrayList getPesquisaTurma( String turmaNome ) {
		String query = "SELECT * FROM turma WHERE nome LIKE '%" + turmaNome + "%' ; ";
		ArrayList<Turma> lstTurma = new ArrayList<Turma>();
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			while( rs.next() ) {
				Turma turmaTemp = new Turma( getCurso( rs.getInt("id_curso") ) );
				turmaTemp.setNome( rs.getString("nome"));
				int idProfessor = rs.getInt( "id_professor" );
				if( idProfessor > 0 ) {
					turmaTemp.setProfessor( getProfessor( idProfessor ) );
				} else {
					turmaTemp.setProfessor(null);
				}
				lstTurma.add( turmaTemp );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return lstTurma;
	}
	
	public boolean overwriteTurma( Turma turmaOld, Turma turmaNew ) {
		String comando = "UPDATE turma SET nome='" + turmaNew.getNome() + "'";
		if( turmaNew.getProfessor() != null ) {
			comando += ", id_professor=" + getProfessorID( turmaNew.getProfessor() ); 
		}
		comando += ", id_curso=" + getCursoID( turmaNew.getCurso() ) + " WHERE id = " + getTurmaID( turmaOld ) + " ;";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			if( turmaNew.getProfessor() != null ) {
				CursoProfessorBD cpbd = new CursoProfessorBD( st );
				cpbd.add( getCursoID( turmaNew.getCurso() ), getProfessorID( turmaNew.getProfessor() ) );
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (CursoProfessorException e) {
			;
		}
		return true;
	}
	
	public ArrayList getArrayTurma() {
		ArrayList<Turma> lstTurma = new ArrayList<Turma>();
		String query = "SELECT * FROM turma;";
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			while( rs.next() ) {
				Turma turmaTemp = new Turma( getCurso( rs.getInt( "id_curso" ) ) );
				turmaTemp.setNome( rs.getString( "nome" ) );
				int idProfessor = rs.getInt( "id_professor" );
				if( idProfessor > 0 ) {
					turmaTemp.setProfessor( getProfessor( idProfessor ) );
				} else {
					turmaTemp.setProfessor( null );
				}
				lstTurma.add( turmaTemp );
				
			}
		} catch ( SQLException e ) {
			e.printStackTrace();
		}
		return lstTurma;
	}
	
	public void removerTurma( Turma turmaTemp ) {
		String comando = "DELETE FROM turma WHERE id=" + getTurmaID( turmaTemp ) + " ;";
		Statement st;
		System.out.println( comando );
		try {
			st = con.createStatement();
			st.execute( comando );
		} catch ( SQLException e ) {
			e.printStackTrace();
		}
	}
	
	public boolean addCurso( Curso novoCurso ) {
		String comando = "INSERT INTO curso VALUES ( NULL," +
				"'" + novoCurso.getNome() + "'," +
				"'" + novoCurso.getCodigo() + "');";
		if( getCursoID(novoCurso) > 0 ) {
			return false;
		}
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	
	public ArrayList getPesquisaCurso( String nomeCurso ) {
		ArrayList<Curso> lstCurso = new ArrayList<Curso>();
		String query = "SELECT * FROM curso WHERE nome LIKE '%" + nomeCurso + "%' ;";
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			while( rs.next() ) {
				Curso cursoTemp = new Curso( rs.getString("nome") , rs.getString( "codigo" ) );
				lstCurso.add( cursoTemp );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return lstCurso;
	}
	
	public boolean overwriteCurso( Curso cursoOld, Curso cursoNew ) {
		String comando = "UPDATE curso SET nome='" + cursoNew.getNome() + "'" +
			", codigo='" + cursoNew.getCodigo() + "' WHERE id = " + getCursoID( cursoOld ) + " ;";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public void removerCurso( Curso cursoTemp ) {
		String comando = "DELETE FROM curso WHERE id=" + getCursoID( cursoTemp ) + " ;";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public boolean overwriteAluno( Aluno alunoOld, Aluno alunoNew ) {
		String comando = "UPDATE aluno " +
			"SET nome='" + alunoNew.getNome() + "'" +
			", matricula='" + alunoNew.getMatricula() + "'";
		ArrayList<Curso> lstCursos = alunoNew.getArrayCurso();
		for( int i = 0; i < lstCursos.size(); i++ ) {
				comando += ", id_curso" + (i + 1) + "='" + getCursoID( alunoNew.getCurso(i) ) + "'";
		}
		comando += " WHERE id = " + getAlunoID( alunoOld ) + " ;";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public boolean overwriteProfessor( Professor professorOld, Professor professorNew ) {
		String comando = "UPDATE professor SET nome='" + professorNew.getNome() + "',matricula='" + professorNew.getMatricula() + "'" +
			"WHERE id=" + getProfessorID( professorOld ) + " ;";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
			con.commit();
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public ArrayList getArrayCurso() {
		ArrayList<Curso> listaCursos = new ArrayList<Curso>();
		Statement st;
		ResultSet rs;
		Curso temp;
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT * FROM curso;");
			while( rs.next() ) {
				temp = new Curso( rs.getString("nome"), rs.getString("codigo") );
				listaCursos.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaCursos;
	}
	
	public ArrayList getArrayAluno() {
		String query = "SELECT * FROM aluno;";
		Statement st;
		ResultSet rs;
		ArrayList<Aluno> listaAlunos = new ArrayList<Aluno>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(query);
			while ( rs.next() ) {
				Aluno alunoTemp = new Aluno( rs.getString( "nome" ) , rs.getString( "matricula" ) );
				Curso cursoTemp1 = getCurso( rs.getInt( "id_curso1" ) );
				if( cursoTemp1 != null ) {
					alunoTemp.addCurso(cursoTemp1);
				}
				Curso cursoTemp2 = getCurso( rs.getInt( "id_curso2" ) );
				if( cursoTemp2 != null ) {
					alunoTemp.addCurso(cursoTemp2);
				}
				Curso cursoTemp3 = getCurso( rs.getInt( "id_curso3" ) );
				if( cursoTemp3 != null ) {
					alunoTemp.addCurso(cursoTemp3);
				}
				listaAlunos.add(alunoTemp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaAlunos;
	}
	
	public ArrayList getArrayProfessor() {
		String query = "SELECT * FROM professor;";
		Statement st;
		ResultSet rs;
		ArrayList<Professor> lstProfessorTemp = new ArrayList<Professor>();
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			while( rs.next() ) {
				Professor professorTemp = new Professor( rs.getString( "nome"), rs.getString( "matricula" ) );
				lstProfessorTemp.add( professorTemp );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lstProfessorTemp;
	}
	
	public ArrayList getPesquisaAluno( String tipo, String dado ) {
		String query = "SELECT * " +
			"FROM aluno " +
			"WHERE " + tipo + " LIKE '%" + dado + "%' ;";
		Statement st;
		ResultSet rs;
		ArrayList<Aluno> listaAlunos = new ArrayList<Aluno>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(query);
			while( rs.next() ) {
				Aluno alunoTemp = new Aluno( rs.getString( "nome" ) , rs.getString( "matricula" ) );
				Curso cursoTemp1 = getCurso( rs.getInt( "id_curso1" ) );
				if( cursoTemp1 != null ) {
					alunoTemp.addCurso(cursoTemp1);
				}
				Curso cursoTemp2 = getCurso( rs.getInt( "id_curso2" ) );
				if( cursoTemp2 != null ) {
					alunoTemp.addCurso(cursoTemp2);
				}
				Curso cursoTemp3 = getCurso( rs.getInt( "id_curso3" ) );
				if( cursoTemp3 != null ) {
					alunoTemp.addCurso(cursoTemp3);
				}
				listaAlunos.add(alunoTemp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaAlunos;
	}
	
	public ArrayList getPesquisaProfessor(String tipo, String dado ) {
		String query = "SELECT * FROM professor WHERE " + tipo + " LIKE '%" + dado + "%' ;";
		Statement st;
		ResultSet rs;
		ArrayList<Professor> lstProfessores = new ArrayList<Professor>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(query);
			while( rs.next() ) {
				Professor professorTemp = new Professor( rs.getString( "nome" ) , rs.getString( "matricula" ) );
				lstProfessores.add(professorTemp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lstProfessores;
	}
	
	public void removerAluno( Aluno alunoTemp ) {
		String comando = "DELETE FROM aluno WHERE id=" + getAlunoID(alunoTemp) + ";";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void removerProfessor( Professor professorTemp ) {
		String comando = "DELETE FROM professor WHERE id=" + getProfessorID( professorTemp ) + " ;";
		Statement st;
		try {
			st = con.createStatement();
			st.execute(comando);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Professor getProfessor( int id ) {
		Professor professorTemp = new Professor();
		String query = "SELECT * FROM professor WHERE id = " + id + " ;";
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			if( rs.next() ) {
				professorTemp.setNome( rs.getString( "nome" ) );
				professorTemp.setMatricula( rs.getString( "matricula" ) );
				return professorTemp;
			}
		} catch( SQLException e ) {
			e.printStackTrace();
			return null;
		}
		return null;
	}
	
	public Curso getCurso( int id ) {
		Curso cursoTemp = new Curso();
		String query = "SELECT nome, codigo FROM curso WHERE id=" + id + ";";
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery(query);
			if( rs.next() ) {
				cursoTemp.setNome( rs.getString( "nome" ) );
				cursoTemp.setCodigo( rs.getString( "codigo" ) );
				return cursoTemp;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
		
	}
	/*
	 * METODOS PRIVADOS
	 */
	
	private int getAlunoID( Aluno alunoTemp ) {
		String query = "SELECT id " +
			"FROM aluno " +
			"WHERE STRCMP( nome , '" + alunoTemp.getNome() + "' ) = 0 " +
			"AND STRCMP( matricula , '" + alunoTemp.getMatricula() + "' ) = 0 ;";
		Statement st;
		ResultSet rs;
		
		try {
			st = con.createStatement();
			rs = st.executeQuery(query);
			if( rs.next() ) {
				return rs.getInt( 1 );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	private int getProfessorID( Professor professorTemp ) {
		String query = "SELECT id FROM professor WHERE STRCMP( nome,'" + professorTemp.getNome() + "' ) = 0 " +
			"AND STRCMP( matricula,'" + professorTemp.getMatricula() + "' ) = 0 ;";
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			if( rs.next() ) {
				return rs.getInt( 1 );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	private int getTurmaID( Turma turmaTemp ) {
		String query = "SELECT id FROM turma WHERE STRCMP( nome, '" + turmaTemp.getNome() + "' ) = 0 " +
			"AND id_curso = " + getCursoID( turmaTemp.getCurso() ) + " ";
		if( turmaTemp.getProfessor() != null ) {
			if( getProfessorID( turmaTemp.getProfessor() ) > 0 ) {
				query += "AND id_professor=" + getProfessorID( turmaTemp.getProfessor() ) + ";";
			}
		} else {
				query += ";";
		}
		Statement st;
		ResultSet rs;
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			if( rs.next() ) {
				return rs.getInt( 1 );
			}
		} catch( SQLException e ) {
			e.printStackTrace();
		}
		return -1;
	}
	
	private int getCursoID( Curso cursoTemp ) {
		String query = "SELECT id " +
			"FROM curso " +
			"WHERE STRCMP( nome , '" + cursoTemp.getNome() + "' ) = 0 " +
			"AND STRCMP( codigo , '" + cursoTemp.getCodigo() + "' ) = 0 ;";
		Statement st;
		ResultSet rs;
		
		try {
			st = con.createStatement();
			rs = st.executeQuery( query );
			if( rs.next() ) {
				return rs.getInt( "id" );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return -1;
	}
	
	private boolean tabelaExiste( String nomeTabela ) throws SQLException {
		Statement st;
		try {
			st = con.createStatement();
			ResultSet rs = st.executeQuery("SHOW TABLES;");
			while( rs.next() ) {
				if( rs.getString(1).compareTo( nomeTabela ) == 0 )
					return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * Metodo privado
	 */
	private void criaTabelas() {
		Statement st;
		try {
			st = con.createStatement();
			st.execute( BancoDadosTabelas.tabelaAluno );
			st.execute( BancoDadosTabelas.tabelaProfessor );
			st.execute( BancoDadosTabelas.tabelaFuncionario );
			st.execute( BancoDadosTabelas.tabelaTurma );
			st.execute( BancoDadosTabelas.tabelaCurso );
			st.execute( BancoDadosTabelas.tabelaTurmaAluno );
			st.execute( BancoDadosTabelas.tabelaCursoProfessor );
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
